--region [dbo].[AddCurrentSymptom]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AddCurrentSymptom]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[AddCurrentSymptom]') IS NOT NULL
	DROP PROCEDURE [dbo].[AddCurrentSymptom]
GO

CREATE PROCEDURE [dbo].[AddCurrentSymptom]
	@PRId int,
	@SymptomId int,
	@DegreeValue int,
	@SymptomHistoryId int
AS

INSERT INTO [PatientCurrentSymptom] (
	[PRId],
	[SymptomId],
	[DegreeValue],
	[SymptomHistoryId]
) VALUES (
	@PRId,
	@SymptomId,
	@DegreeValue,
	@SymptomHistoryId
)

GO
--endregion


--region [dbo].[UpdateCurrentSymptom]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateCurrentSymptom]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateCurrentSymptom]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateCurrentSymptom]
GO

CREATE PROCEDURE [dbo].[UpdateCurrentSymptom]
	@PRId int,
	@SymptomId int,
	@DegreeValue int,
	@SymptomHistoryId int
AS

UPDATE [PatientCurrentSymptom] SET
	[DegreeValue] = @DegreeValue,
	[SymptomHistoryId] = @SymptomHistoryId
WHERE
	[PRId] = @PRId AND
	[SymptomId] = @SymptomId

GO
--endregion


--region [dbo].[DelCurrentSymptom]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[DelCurrentSymptom]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[DelCurrentSymptom]') IS NOT NULL
	DROP PROCEDURE [dbo].[DelCurrentSymptom]
GO

CREATE PROCEDURE [dbo].[DelCurrentSymptom]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
DELETE FROM
	[PatientCurrentSymptom]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetCurrentSymptom]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetCurrentSymptom]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetCurrentSymptom]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetCurrentSymptom]
GO

CREATE PROCEDURE [dbo].[GetCurrentSymptom]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT
	''Id'' = [PatientSymptomHistory].[Id],
	''PRId'' = [PatientCurrentSymptom].[PRId],
	''SymptomId'' = [PatientCurrentSymptom].[SymptomId],
	''DegreeValue'' = [PatientCurrentSymptom].[DegreeValue],
	[RecordDate],
	[UpdateDate]
FROM
	[PatientCurrentSymptom], [PatientSymptomHistory]
WHERE
	[PatientCurrentSymptom].[SymptomHistoryId] = [PatientSymptomHistory].[Id] AND
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetCurrentSymptomList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetCurrentSymptomList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetCurrentSymptomList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetCurrentSymptomList]
GO

CREATE PROCEDURE [dbo].[GetCurrentSymptomList]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	''Id'' = [PatientSymptomHistory].[Id],
	''PRId'' = [PatientCurrentSymptom].[PRId],
	''SymptomId'' = [PatientCurrentSymptom].[SymptomId],
	[SymptomName],
	''DegreeValue'' = [PatientCurrentSymptom].[DegreeValue],
	[SurveyId],
	[RecordDate],
	[UpdateDate],
	[PatientSymptomHistory].[Type]
FROM
	[PatientCurrentSymptom], [PatientSymptomHistory], [Symptom]
WHERE
	[PatientCurrentSymptom].[SymptomHistoryId] = [PatientSymptomHistory].[Id] AND
	[PatientCurrentSymptom].[SymptomId] = [Symptom].[Id] AND
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetCurrentSymptomCount]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetCurrentSymptomCount]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetCurrentSymptomCount]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetCurrentSymptomCount]
GO

CREATE PROCEDURE [dbo].[GetCurrentSymptomCount]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id)
FROM
	[PatientCurrentSymptom]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion



--region [dbo].[AddSymptomHistory]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AddSymptomHistory]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[AddSymptomHistory]') IS NOT NULL
	DROP PROCEDURE [dbo].[AddSymptomHistory]
GO

CREATE PROCEDURE [dbo].[AddSymptomHistory]
	@PRId int,
	@SymptomId int,
	@SurveyId int,
	@DegreeValue int,
	@RecordDate datetime,
	@UpdateDate datetime,
	@Remark nvarchar(2000),
	@Type int,
	@id int OUTPUT
AS

INSERT INTO [PatientSymptomHistory] (
	[PRId],
	[SymptomId],
	[SurveyId],
	[DegreeValue],
	[RecordDate],
	[UpdateDate],
	[Remark],
	[Type]
) VALUES (
	@PRId,
	@SymptomId,
	@SurveyId,
	@DegreeValue,
	@RecordDate,
	@UpdateDate,
	@Remark,
	@Type
)

SET @id = SCOPE_IDENTITY()

GO
--endregion


--region [dbo].[UpdateSymptomHistory]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateSymptomHistory]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateSymptomHistory]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateSymptomHistory]
GO

CREATE PROCEDURE [dbo].[UpdateSymptomHistory]
	@Id int,
	@PRId int,
	@SymptomId int,
	@SurveyId int,
	@DegreeValue int,
	@RecordDate datetime,
	@UpdateDate datetime,
	@Remark nvarchar(2000)
AS

UPDATE [PatientSymptomHistory] SET
	[SurveyId] = @SurveyId,
	[DegreeValue] = @DegreeValue,
	[RecordDate] = @RecordDate,
	[UpdateDate] = @UpdateDate,
	[Remark] = @Remark
WHERE
	[PRId] = @PRId AND [SymptomId] = @SymptomId AND [Id] = @Id 
GO
--endregion


--region [dbo].[DelSymptomHistory]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[DelSymptomHistory]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[DelSymptomHistory]') IS NOT NULL
	DROP PROCEDURE [dbo].[DelSymptomHistory]
GO

CREATE PROCEDURE [dbo].[DelSymptomHistory]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
DELETE FROM
	[PatientSymptomHistory]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetSymptomHistory]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetSymptomHistory]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetSymptomHistory]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetSymptomHistory]
GO

CREATE PROCEDURE [dbo].[GetSymptomHistory]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT
	''Id'' = [PatientSymptomHistory].[Id],
	[PRId],
	[SymptomId],
	[SymptomName],
	[SurveyId],
	[DegreeValue],
	[RecordDate],
	[UpdateDate],
	[Remark]
FROM
	[PatientSymptomHistory], [Symptom]
WHERE
	[PatientSymptomHistory].[SymptomId] = [Symptom].[Id] AND
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetSymptomHistoryList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetSymptomHistoryList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetSymptomHistoryList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetSymptomHistoryList]
GO

CREATE PROCEDURE [dbo].[GetSymptomHistoryList]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id) 
FROM [PatientSymptomHistory]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT TOP ' + STR(@pagesize) + '
	''Id'' = [PatientSymptomHistory].[Id],
	[PRId],
	[SymptomId],
	[SymptomName],
	[SurveyId],
	[DegreeValue],
	[RecordDate],
	[UpdateDate],
	[Remark],
	[PatientSymptomHistory].[Type]
FROM
	[PatientSymptomHistory], [Symptom]
WHERE
	[PatientSymptomHistory].[SymptomId] = [Symptom].[Id]
	'

IF @PageIndex > 1
BEGIN
SET @SQL = @SQL + '
	AND [PatientSymptomHistory].[Id] < 
	( 
	SELECT MIN([Id])  
	FROM ( 
	SELECT TOP 
		' + STR((@PageIndex-1)*@PageSize) + ' [Id] 
	FROM [PatientSymptomHistory] ' 

	IF @WhereCondition <> ''
	BEGIN
		SET @SQL = @SQL + ' AND ' + @WhereCondition
	END

	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression + ') AS tblTmp ) '
	END
	ELSE
	BEGIN
		SET @SQL = @SQL + ') AS tblTmp ) '
	END

	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression 
	END	
END
ELSE 
BEGIN
	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + ' 
	ORDER BY
		' + @OrderByExpression 
	END	
END

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetSurveyHistorySymptomList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetSurveyHistorySymptomList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetSurveyHistorySymptomList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetSurveyHistorySymptomList]
GO

CREATE PROCEDURE [dbo].[GetSurveyHistorySymptomList]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	''Id'' = [PatientSymptomHistory].[Id],
	[PRId],
	[SymptomId],
	[SymptomName],
	[SurveyId],
	[DegreeValue],
	[RecordDate],
	[UpdateDate],
	[Remark]
FROM
	[PatientSymptomHistory], [Symptom]
WHERE
	[PatientSymptomHistory].[SymptomId] = [Symptom].[Id] AND
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetSymptomHistoryCount]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetSymptomHistoryCount]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetSymptomHistoryCount]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetSymptomHistoryCount]
GO

CREATE PROCEDURE [dbo].[GetSymptomHistoryCount]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id)
FROM
	[PatientSymptomHistory]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetAllSymptomHistory]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetSymptomHistoryList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetAllSymptomHistory]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetAllSymptomHistory]
GO

CREATE PROCEDURE [dbo].[GetAllSymptomHistory]
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	''Id'' = [PatientSymptomHistory].[Id],
	[PRId],
	[SymptomId],
	[SymptomName],
	[SurveyId],
	[DegreeValue],
	[RecordDate],
	[UpdateDate],
	[Type],
	[Remark],
	[PatientSymptomHistory].[Type]
FROM
	[PatientSymptomHistory], [Symptom]
WHERE
	[PatientSymptomHistory].[SymptomId] = [Symptom].[Id]
	'

IF @WhereCondition <> ''
BEGIN
SET @SQL = @SQL + ' AND ' + @WhereCondition
END

IF @OrderByExpression <> ''
BEGIN
	SET @SQL = @SQL + ' 
ORDER BY
	' + @OrderByExpression 
END	

EXEC sp_executesql @SQL

GO
--endregion

